package com.ews.onlineexchange.util;

import java.io.BufferedInputStream;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;

import javax.faces.application.FacesMessage;
import javax.faces.context.FacesContext;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.primefaces.model.DefaultStreamedContent;
import org.primefaces.model.StreamedContent;

public class WorkBookUtil {
	public static final String EXPORT_DATA_DEFAULT_PATH = "c://test.xls"; 
	public static final String CREATE_EXCEL_ERROR_TIP = "生成Excel文件错误，请稍后重试！";
	public Workbook getWorkBookFromBytes(byte[] bytes) throws Exception {
		InputStream in = new ByteArrayInputStream(bytes);
		Workbook workbook = WorkbookFactory.create(in);
		return workbook;
	}
	
	
  /**
   * 创建带表头的workbook
 * @param sheetName
 * @param FIELD_LABEL_ARRAY
 * @return
 */
	public HSSFWorkbook createWorkbook(String sheetName,String[] FIELD_LABEL_ARRAY) {
		  
		  //声明一个工作簿  
	      HSSFWorkbook hwb = new HSSFWorkbook();  
	      //创建sheet页
	      HSSFSheet sheet = hwb.createSheet(sheetName); 
	      sheet.setDefaultColumnWidth((short)15);  
	      //生成一个样式  
	      HSSFCellStyle style = hwb.createCellStyle();  
	      style.setAlignment(HorizontalAlignment.CENTER);
	      style.setFillBackgroundColor(HSSFColor.BLUE.index);
	      HSSFFont font = hwb.createFont();
	      font.setColor(HSSFColor.WHITE.index);
	      style.setFont(font);
	      //生成表头  
	      HSSFRow row = sheet.createRow(0);  
	      for(int i = 0;i<FIELD_LABEL_ARRAY.length;i++) {
	    	//给表头第一行一次创建单元格  
	          row.createCell(i,CellType.STRING).setCellValue(FIELD_LABEL_ARRAY[i]);  
	      }
	      
	      return hwb;
	}
	
	public HSSFWorkbook createWorkbookMergingCell(String sheetName,String[] FIELD_LABEL_ARRAY_FIRST,String[] FIELD_LABEL_ARRAY_SECOND,int firstColIndex,int secondColIndex) {
		  
		  //声明一个工作簿  
	      HSSFWorkbook hwb = new HSSFWorkbook();  
	      //创建sheet页
	      HSSFSheet sheet = hwb.createSheet(sheetName); 
	      sheet.setDefaultColumnWidth((short)15);  
	      //生成一个样式  
	      HSSFCellStyle style = hwb.createCellStyle();  
	      style.setAlignment(HorizontalAlignment.CENTER);
	      style.setFillBackgroundColor(HSSFColor.BLUE.index);
//	      HSSFFont font = hwb.createFont();
//	      font.setColor(HSSFColor.WHITE.index);
//	      style.setFont(font);
	      //生成表头  
	      HSSFRow row = sheet.createRow(0);  
	      for(int i = firstColIndex;i<FIELD_LABEL_ARRAY_FIRST.length;i++) {
	    	//给表头第一行一次创建单元格  
	    	  HSSFCell cell = row.createCell(i,CellType.STRING);
	    	  cell.setCellValue(FIELD_LABEL_ARRAY_FIRST[i]); 
	          cell.setCellStyle(style);
	      }
	      HSSFRow row2 = sheet.createRow(1);  
	      for(int i = 0;i < FIELD_LABEL_ARRAY_SECOND.length;i++) {
	    	//给表头第一行一次创建单元格  
	    	 HSSFCell cell = row2.createCell(i+secondColIndex,CellType.STRING);
			 cell.setCellValue(FIELD_LABEL_ARRAY_SECOND[i]); 
			 cell.setCellStyle(style);
	      }
    	  
    	  CellRangeAddress region = new CellRangeAddress(0, 0, 2, 8);// 下标从0开始
    	  sheet.addMergedRegion(region);
    	  CellRangeAddress region1 = new CellRangeAddress(0, 1, 0, 0);// 下标从0开始
    	  sheet.addMergedRegion(region1);
    	  CellRangeAddress region2 = new CellRangeAddress( 0, 1, 1, 1);// 下标从0开始
    	  sheet.addMergedRegion(region2);
	      return hwb;
	}
	
	public static StreamedContent getSteamedContentByWorkBook(String fileName, Workbook workbook) {		
		try {
			File tempFile = File.createTempFile("CREDITMANAGER", "TEMPFILE");
			if (!tempFile.exists()) {
				tempFile.createNewFile();
			}
			FileOutputStream fileOut = new FileOutputStream(tempFile);
			workbook.write(fileOut);
			fileOut.close();
			InputStream is = new BufferedInputStream(new BufferedInputStream(new FileInputStream(tempFile)));
			return new DefaultStreamedContent(is, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
					fileName);
		} catch (Exception e) {
			e.printStackTrace();
			FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR,"生成Excel出现错误！","");
	        FacesContext.getCurrentInstance().addMessage(null, message);
	        return null;
		}
	}
	
	
	/**
	 * 设置单元格上提示
	 * @param sheet  要设置的sheet.
	 * @param promptTitle 标题
	 * @param promptContent 内容
	 * @param firstRow 开始行
	 * @param endRow  结束行
	 * @param firstCol  开始列
	 * @param endCol  结束列
	 * @return 设置好的sheet.
	 */
	public static HSSFSheet setHSSFPrompt(HSSFSheet sheet, String promptTitle,String promptContent, int firstRow, int endRow ,int firstCol,int endCol) {
		// 构造constraint对象
		DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("BB1");
		// 四个参数分别是：起始行、终止行、起始列、终止列
		CellRangeAddressList regions = new CellRangeAddressList(firstRow,endRow,firstCol, endCol);
		// 数据有效性对象
		HSSFDataValidation data_validation_view = new HSSFDataValidation(regions,constraint);
		data_validation_view.createPromptBox(promptTitle, promptContent);
		sheet.addValidationData(data_validation_view);
		return sheet;
	}
	
}
